"""
SqlAlchermy subquery实现复杂查询
"""
import os
import sys

path = os.path.dirname(__file__)
sys.path.append(os.path.join(path, '..'))

import random
from faker import Faker
from sqlalchemy import Column, Integer, String
from conf.mysql_config import SESSION, BASE


class CustomerOrm(BASE):
    __tablename__ = 'customer'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(20), comment='客户姓名', doc={'zh': '客户姓名'})
    age = Column(Integer, comment='客户年龄', doc={'zh': '客户年龄'})
    city = Column(String(50), comment='客户所在城市', doc={'zh': '客户所在城市'})

    def __str__(self):
        return f'<CustomerOrm name={self.name}, age={self.age}, city={self.city}>'

    __repr__ = __str__


BASE.metadata.create_all()


def add_data():
    fake = Faker(locale='zh_CN')
    cities = ['杭州', '上海', '温州', '苏州', '南京', '盐城', '青岛', '秦皇岛', '天津', '北京', '福州', '泉州',
              '深圳', '广州', '佛山', '重庆']
    customers = [CustomerOrm(name=fake.name(), age=random.randint(20, 35), city=random.choice(cities))
                 for _ in range(1000)]
    SESSION.add_all(customers)
    SESSION.commit()


def search_data(name: str = ''):
    # 需求寻找和name这个人在同一个城市，并且是同年龄的人
    if not isinstance(name, str) or not name:
        return
    # 不使用子查询版本
    user = SESSION.query(CustomerOrm).filter(CustomerOrm.name == name).first()
    print(user)
    results = SESSION.query(CustomerOrm).filter(CustomerOrm.city == user.city, CustomerOrm.age == user.age).all()
    print(results)
    # 使用子查询
    subquery = SESSION.query(CustomerOrm.age.label('age'), CustomerOrm.city.label('city')).filter(CustomerOrm.name == name).subquery()
    results = SESSION.query(CustomerOrm).filter(CustomerOrm.city == subquery.c.city, CustomerOrm.age == subquery.c.age).all()
    print(results)


if __name__ == '__main__':
    search_data('唐瑞')
